回顧一下上篇提到的binlog紀錄作用,可以用來做資料復原和主從機制下的重要數據紀錄。
開始前先了解一下如何使用<< mysqldump >>指令做數據庫備份
常用的幾個舉例如下:
mysqldump [options] db_name [tbl_name ...]
mysqldump [options] --databases db_name ...
mysqldump [options] --all-databases
(資料備份)
備份單一資料庫
# mysqldump -u root -p db_name > back_up.sql;
備份資料庫中一or多個資料表
# mysqldump -u root -p db_name tbl_name tbl_name2 > back_up.sql;
備份所有資料庫
# mysqldump -u root -p --all-databases > back_up.sql;
(資料還原)
復原多個資料庫
# mysql -u root -p < back_up.sql
復原單一資料庫
# mysql -u root -p database_name < back_up.sql
當然以上只是基礎的備份~還有像只備結構或數據等...參數使用參考文章。 mysqldump參數用法
有了平時的備份數據,當發生誤刪數據狀況時,如何藉由binlog紀錄找到發生點做到數據恢復?
了解binlog內容,利用 << mysqlbinlog >> 找events pos紀錄位置。
以下 2 種瀏覽binlog方式:
1.原始binlog紀錄檔 (擷取執行SQL: delete from members where id =5 的一段完整紀錄)
2.利用 Mysql cli 查詢binlog紀錄。
紀錄檔 mysql-bin.00000x
圖.1
# at 425
#210810 2:38:58 server id 1 end_log_pos 456 CRC32 0xc26fb981 Xid = 605
COMMIT/*!*/;
# at 456
#210810 2:59:55 server id 1 end_log_pos 521 CRC32 0x93934176 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 521
#210810 2:59:55 server id 1 end_log_pos 593 CRC32 0x368339f5 Query thread_id=6 exec_time=0 error_code=0
SET TIMESTAMP=1628564395/*!*/;
BEGIN
/*!*/;
# at 593
#210810 2:59:55 server id 1 end_log_pos 646 CRC32 0x799d4af6 Table_map: `user`.`members` mapped to number 110
# at 646
#210810 2:59:55 server id 1 end_log_pos 705 CRC32 0xfa28c00a Delete_rows: table id 110 flags: STMT_END_F
BINLOG '
q+sRYRMBAAAANQAAAIYCAAAAAG4AAAAAAAEABHVzZXIAB21lbWJlcnMAAwj8CAECAPZKnXk=
q+sRYSABAAAAOwAAAMECAAAAAG4AAAAAAAEAAgAD//gFAAAAAAAAAAUAc2lhbmdYYMUJAAAAAArA
KPo=
'/*!*/;
### DELETE FROM `user`.`members`
### WHERE
### @1=5
### @2='siang'
### @3=163930200
# at 705
#210810 2:59:55 server id 1 end_log_pos 736 CRC32 0xe959995d Xid = 607
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
圖.2
指定POS查詢
mysql> show binlog events in 'mysql-bin.000001' from 425 limit 10;
不過在生產環境上資料是持續在異動的,所以log紀錄會很龐大,不會直接打開binlog檔去找操作紀錄位置。得透過一些過濾的查詢方便我們在找紀錄上快速定位。
ex. 查詢這句SQL指令POS位置 (delete from members where id =5 ) 的2種方式~
(一)藉由SQL指令關鍵字
# mysqlbinlog -v mysql-bin.000001 | cat -n | grep -iw 'delete'
查詢結果:
74 ### DELETE FROM `user`.`members`
# mysqlbinlog -v mysql-bin.000001 | cat -n | sed -n '50,81p'
查詢結果:
50 ### @3=163930200
....
62 BEGIN
....
65 #210810 2:59:55 server id 1 end_log_pos 646 CRC32 0x799d4af6 Table_map: `user`.`members` mapped to number 110
66 # at 646
67 #210810 2:59:55 server id 1 end_log_pos 705 CRC32 0xfa28c00a Delete_rows: table id 110 flags: STMT_END_F
....
74 ### DELETE FROM `user`.`members`
75 ### WHERE
76 ### @1=5
77 ### @2='siang'
78 ### @3=163930200
....
81 COMMIT/*!*/;
(二)藉由限制發生時間和指定資料庫
# mysqlbinlog -v --start-datetime="2021-08-10 02:59:00" --database=user --base64-output=decode-rows mysql-bin.000001
下集預告: 知道備份和binlog紀錄怎麼使用後,明天來準備跑模擬流程的資料